package cn.iocoder.yudao.module.product.statistics.dal.mysql;

import cn.iocoder.yudao.framework.business.basic.dao.mapper.CoreMapper;
import cn.iocoder.yudao.framework.business.basic.dao.query.CoreLambdaQueryWrapper;
import cn.iocoder.yudao.framework.common.pojo.PageResult;
import cn.iocoder.yudao.framework.common.pojo.SortablePageParam;
import cn.iocoder.yudao.framework.mybatis.core.query.MPJLambdaWrapperX;
import cn.iocoder.yudao.module.product.statistics.dal.dataobject.ProductStatisticsDO;
import cn.iocoder.yudao.module.product.statistics.vo.ProductStatisticsReqVO;
import cn.iocoder.yudao.module.product.statistics.vo.ProductStatisticsRespVO;
import com.baomidou.mybatisplus.core.metadata.IPage;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;

import static cn.iocoder.yudao.framework.mybatis.core.util.MyBatisUtils.toUnderlineCase;

/**
 * 产品统计 Mapper
 *
 * @author owen
 */
@Mapper
public interface ProductStatisticsMapper extends CoreMapper<ProductStatisticsDO> {

    default PageResult<ProductStatisticsDO> selectPageGroupBySpuId(ProductStatisticsReqVO reqVO, SortablePageParam pageParam) {
        return selectPage(pageParam, buildWrapper(reqVO)
                .select(ProductStatisticsDO::getSpuId)
                .groupBy(ProductStatisticsDO::getSpuId)
        );
    }

    default List<ProductStatisticsDO> selectListByTimeBetween(ProductStatisticsReqVO reqVO) {
        return selectList(buildWrapper(reqVO)
                .select(ProductStatisticsDO::getTime)
                .groupBy(ProductStatisticsDO::getTime));
    }

    default ProductStatisticsRespVO selectVoByTimeBetween(ProductStatisticsReqVO reqVO) {
        return selectJoinOne(ProductStatisticsRespVO.class, buildWrapper(reqVO));
    }

    /**
     * 构建 LambdaWrapper
     *
     * @param reqVO 查询参数
     * @return LambdaWrapper
     */
    private static MPJLambdaWrapperX<ProductStatisticsDO> buildWrapper(ProductStatisticsReqVO reqVO) {
        return new MPJLambdaWrapperX<ProductStatisticsDO>()
                .betweenIfPresent(ProductStatisticsDO::getTime, reqVO.getTimes())
                .selectSum(ProductStatisticsDO::getBrowseCount, toUnderlineCase(ProductStatisticsDO::getBrowseCount))
                .selectSum(ProductStatisticsDO::getBrowseUserCount, toUnderlineCase(ProductStatisticsDO::getBrowseUserCount))
                .selectSum(ProductStatisticsDO::getFavoriteCount, toUnderlineCase(ProductStatisticsDO::getFavoriteCount))
                .selectSum(ProductStatisticsDO::getCartCount, toUnderlineCase(ProductStatisticsDO::getCartCount))
                .selectSum(ProductStatisticsDO::getOrderCount, toUnderlineCase(ProductStatisticsDO::getOrderCount))
                .selectSum(ProductStatisticsDO::getOrderPayCount, toUnderlineCase(ProductStatisticsDO::getOrderPayCount))
                .selectSum(ProductStatisticsDO::getOrderPayPrice, toUnderlineCase(ProductStatisticsDO::getOrderPayPrice))
                .selectSum(ProductStatisticsDO::getAfterSaleCount, toUnderlineCase(ProductStatisticsDO::getAfterSaleCount))
                .selectSum(ProductStatisticsDO::getAfterSaleRefundPrice, toUnderlineCase(ProductStatisticsDO::getAfterSaleRefundPrice))
                .selectAvg(ProductStatisticsDO::getBrowseConvertPercent, toUnderlineCase(ProductStatisticsDO::getBrowseConvertPercent));
    }

    /**
     * 根据时间范围统计产品信息
     *
     * @param page      分页参数
     * @param beginTime 起始时间
     * @param endTime   截止时间
     * @return 统计
     */
    @Select("""
                    SELECT spu.id                                                       AS spuId
                         -- 浏览量：一个用户可以有多次
                         , (SELECT COUNT(1)
                            FROM product_browse_history
                            WHERE spu_id = spu.id
                              AND create_time BETWEEN #{beginTime} AND #{endTime})      AS browse_count
                         -- 访客量：按用户去重计数
                         , (SELECT COUNT(DISTINCT user_id)
                            FROM product_browse_history
                            WHERE spu_id = spu.id
                              AND create_time BETWEEN #{beginTime} AND #{endTime})      AS browse_user_count
                         -- 收藏数量：按用户去重计数
                         , (SELECT COUNT(DISTINCT user_id)
                            FROM product_favorite
                            WHERE spu_id = spu.id
                              AND create_time BETWEEN #{beginTime} AND #{endTime})      AS favorite_count
                         -- 加购数量：按用户去重计数
                         , (SELECT COUNT(DISTINCT user_id)
                            FROM trade_cart
                            WHERE spu_id = spu.id
                              AND create_time BETWEEN #{beginTime} AND #{endTime})      AS cart_count
                         -- 下单件数
                         , (SELECT IFNULL(SUM(count), 0)
                            FROM trade_order_item
                            WHERE spu_id = spu.id
                              AND create_time BETWEEN #{beginTime} AND #{endTime})      AS order_count
                         -- 支付件数
                         , (SELECT IFNULL(SUM(item.count), 0)
                            FROM trade_order_item item
                                     JOIN trade_order o ON item.order_id = o.id
                            WHERE spu_id = spu.id
                              AND o.pay_status = TRUE
                              AND item.create_time BETWEEN #{beginTime} AND #{endTime}) AS order_pay_count
                         -- 支付金额
                         , (SELECT IFNULL(SUM(item.pay_price), 0)
                            FROM trade_order_item item
                                     JOIN trade_order o ON item.order_id = o.id
                            WHERE spu_id = spu.id
                              AND o.pay_status = TRUE
                              AND item.create_time BETWEEN #{beginTime} AND #{endTime}) AS order_pay_price
                         -- 退款件数
                         , (SELECT IFNULL(SUM(count), 0)
                            FROM trade_after_sale
                            WHERE spu_id = spu.id
                              AND refund_time IS NOT NULL
                              AND create_time BETWEEN #{beginTime} AND #{endTime})      AS after_sale_count
                         -- 退款金额
                         , (SELECT IFNULL(SUM(refund_price), 0)
                            FROM trade_after_sale
                            WHERE spu_id = spu.id
                              AND refund_time IS NOT NULL
                              AND create_time BETWEEN #{beginTime} AND #{endTime})      AS after_sale_refund_price
                    FROM product_spu spu
                    WHERE spu.deleted = FALSE
                    ORDER BY spu.id
            """)
    IPage<ProductStatisticsDO> selectStatisticsResultPageByTimeBetween(IPage<ProductStatisticsDO> page,
                                                                       @Param("beginTime") LocalDateTime beginTime,
                                                                       @Param("endTime") LocalDateTime endTime);

    default Long selectCountByTimeBetween(LocalDateTime beginTime, LocalDateTime endTime) {
        return selectCount(new CoreLambdaQueryWrapper<ProductStatisticsDO>().between(ProductStatisticsDO::getTime, beginTime, endTime));
    }

    @Select("""
            SELECT count(1)
            FROM product_spu AS spu
            WHERE spu.deleted = FALSE
            """)
    Integer selectProductCount();

    @Select("""
            SELECT count(1)
            FROM product_spu AS spu
            WHERE spu.status = 0 AND spu.deleted = FALSE
            """)
    Integer selectEnabledProductCount();

}